﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Catering.View_Operaction;
using System.Data;
using System.Text;
using System.Collections;
using System.Web.Services;
using Catering_Rules;
using Microsoft.Office.Core;
using System.IO;
using Catering_DBOpertion;


namespace Catering.Catering_Page.yl.Daily
{
    public partial class DailyEMtce : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                txt_stTime.Attributes.Add("onfocus", "WdatePicker({dateFmt:'yyyy/MM/dd'})");
                txt_endTime.Attributes.Add("onfocus", "WdatePicker({dateFmt:'yyyy/MM/dd'})");
                txt_stTime.Text = DateTime.Now.AddDays(-7).ToString("yyyy/MM/dd");
                txt_endTime.Text = DateTime.Now.ToString("yyyy/MM/dd");
                LoadDrop();
                LoadForm();
            }
        }
        protected void LoadDrop()
        {
            if (Session["User"] != null)
            {
                string userid = Session["PersonnelID"].ToString();
                int companyid = 0;
                int groupid = 0;
                int classid = 0;
                bool isAdmin = false;
                Bind_Data_to_Controls.GetCompanyByCurrentUser(userid, out companyid, out groupid, out classid, out isAdmin);
                Bind_Data_to_Controls.Bind_Area_ddl(ddl_group, isAdmin, groupid, companyid, true, false);
                Bind_Data_to_Controls.Bind_Class_ddl(ddl_group.SelectedItem, ddl_Class, isAdmin, classid, true);
            }
        }
        protected void ddl_group_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddl_group.SelectedItem != null)
            {
                Bind_Data_to_Controls.Bind_Class_ddl(ddl_group.SelectedItem, ddl_Class);
            }
        }

        protected void Drop_BanZu_SelectedIndexChanged(object sender, EventArgs e)
        {
            //DataTable dt_BanZu = new Catering_Rules.ClassInfo().GetList("AreaID=" + this.Drop_ZuoYeQu.SelectedValue).Tables[0];
            //this.DropBind(this.Drop_BanZu, dt_BanZu, "id", "CName");            
        }
        //公用的DropDownList绑定方法
        public void DropBind(DropDownList drop, DataTable dt, string strValue, string strText)
        {
            drop.Items.Clear();
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ListItem item = new ListItem();
                    item.Text = dt.Rows[i][strText].ToString();
                    item.Value = dt.Rows[i][strValue].ToString();
                    drop.Items.Add(item);
                }
            }
        }
        public DataSet SelConNameByConID(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 CName");
            strSql.Append(" FROM Contractor ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where id = " + int.Parse(strWhere));
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        public DataSet SelCNameByCID(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 CName");
            strSql.Append(" FROM ClassInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where id = " + int.Parse(strWhere));
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        public void LoadForm()
        {
            string strTmp = "";
            if (ddl_group.SelectedItem.Text == "全部" && ddl_Class.SelectedItem.Text == "全部")
                strTmp = "AddDate between '" + txt_stTime.Text + "' and '" + txt_endTime.Text + "'";            
            if (ddl_group.SelectedItem.Text != "全部" && ddl_Class.SelectedItem.Text == "全部")
                strTmp = " GroupID = '" + ddl_group.SelectedValue + "' and AddDate between '" + txt_stTime.Text + "' and '" + txt_endTime.Text + "'";
            if (ddl_group.SelectedItem.Text != "全部" && ddl_Class.SelectedItem.Text != "全部")
                strTmp = " GroupID = '" + ddl_group.SelectedValue + "' and ClassID = '" + ddl_Class.SelectedValue + "' and AddDate between '" + txt_stTime.Text + "' and '" + txt_endTime.Text + "'";
            try
            {
                DataSet ds = new Catering_Rules.EMASppPlanE().GetList(strTmp);
                DataTable dt_Plan_old = ds.Tables[0];
                //除去重复的行
                DataTable dt_Plan = dt_Plan_old.Clone();
                foreach (DataRow dr in dt_Plan_old.Rows)
                {
                    DataRow dr_Plan = dt_Plan.NewRow();

                    bool bFlag = true;
                    for (int nCount = 0; nCount < dt_Plan.Rows.Count; nCount++)
                    {
                        if (dr["ClassID"].ToString() == dt_Plan.Rows[nCount]["ClassID"].ToString() &&
                            dr["ContractorID"].ToString() == dt_Plan.Rows[nCount]["ContractorID"].ToString() &&
                            dr["AddDate"].ToString() == dt_Plan.Rows[nCount]["AddDate"].ToString())
                            bFlag = false;
                    }
                    if (bFlag)
                    {
                        foreach (DataColumn dc in dt_Plan_old.Columns)
                        {
                            dr_Plan[dc.ColumnName] = dr[dc.ColumnName];
                        }
                        dt_Plan.Rows.Add(dr_Plan);
                    }
                }
                dt_Plan.Columns.Add("tempId");                
                dt_Plan.Columns.Add("ClassName");
                dt_Plan.Columns.Add("ContractorName");
                dt_Plan.Columns.Add("ItemsDate");

                for (int i = 0; i < dt_Plan.Rows.Count; i++)
                {
                    dt_Plan.Rows[i]["ClassName"] = SelCNameByCID(dt_Plan.Rows[i]["ClassID"].ToString()).Tables[0].Rows[0][0];
                    dt_Plan.Rows[i]["ContractorName"] = SelConNameByConID(dt_Plan.Rows[i]["ContractorID"].ToString()).Tables[0].Rows[0][0];
                    dt_Plan.Rows[i]["tempId"] = i + 1;
                    dt_Plan.Rows[i]["ItemsDate"] = Convert.ToDateTime(dt_Plan.Rows[i]["AddDate"].ToString()).ToString("yyyy/MM/dd");
                }
                this.ERep_Plan.DataSource = dt_Plan;
                this.ERep_Plan.DataBind();
            }
            catch
            {

            }
        }

        protected void Search_Click(object sender, EventArgs e)
        {
            string strTmp = "";
            if (ddl_group.SelectedItem.Text == "全部" && ddl_Class.SelectedItem.Text == "全部")
                strTmp = "AddDate between '" + txt_stTime.Text + "' and '" + txt_endTime.Text + "'";
            if (ddl_group.SelectedItem.Text != "全部" && ddl_Class.SelectedItem.Text == "全部")
                strTmp = " GroupID = '" + ddl_group.SelectedValue + "' and AddDate between '" + txt_stTime.Text + "' and '" + txt_endTime.Text + "'";
            if (ddl_group.SelectedItem.Text != "全部" && ddl_Class.SelectedItem.Text != "全部")
                strTmp = " GroupID = '" + ddl_group.SelectedValue + "' and ClassID = '" + ddl_Class.SelectedValue + "' and AddDate between '" + txt_stTime.Text + "' and '" + txt_endTime.Text + "'";
            if (ddl_type.Text == "设备报修单")
            {
                ERepair_list.Visible = true;
                PRepair_list.Visible = false;                
                try
                {
                    DataSet ds = new Catering_Rules.EMASppPlanE().GetList(strTmp);
                    DataTable dt_Plan_old = ds.Tables[0];
                    //除去重复的行
                    DataTable dt_Plan = dt_Plan_old.Clone(); 
                    foreach (DataRow dr in dt_Plan_old.Rows)
                    {
                        DataRow dr_Plan = dt_Plan.NewRow();

                        bool bFlag = true;
                        for (int nCount = 0; nCount < dt_Plan.Rows.Count; nCount++ )
                        {
                            if (dr["ClassID"].ToString() == dt_Plan.Rows[nCount]["ClassID"].ToString() &&
                                dr["ContractorID"].ToString() == dt_Plan.Rows[nCount]["ContractorID"].ToString() &&
                                dr["AddDate"].ToString() == dt_Plan.Rows[nCount]["AddDate"].ToString())
                                bFlag = false;
                        }
                        if(bFlag)
                        {
                            foreach (DataColumn dc in dt_Plan_old.Columns)
                            {
                                dr_Plan[dc.ColumnName] = dr[dc.ColumnName];
                            }
                            dt_Plan.Rows.Add(dr_Plan);
                        }
                    }

                    dt_Plan.Columns.Add("tempId");
                    dt_Plan.Columns.Add("ClassName");
                    dt_Plan.Columns.Add("ContractorName");
                    dt_Plan.Columns.Add("ItemsDate");

                    for (int i = 0; i < dt_Plan.Rows.Count; i++)
                    {
                        dt_Plan.Rows[i]["ClassName"] = SelCNameByCID(dt_Plan.Rows[i]["ClassID"].ToString()).Tables[0].Rows[0][0];
                        dt_Plan.Rows[i]["ContractorName"] = SelConNameByConID(dt_Plan.Rows[i]["ContractorID"].ToString()).Tables[0].Rows[0][0];
                        dt_Plan.Rows[i]["tempId"] = i + 1;
                        dt_Plan.Rows[i]["ItemsDate"] = Convert.ToDateTime(dt_Plan.Rows[i]["AddDate"].ToString()).ToString("yyyy/MM/dd");
                    }
                    this.ERep_Plan.DataSource = dt_Plan;
                    this.ERep_Plan.DataBind();
                }
                catch
                {

                }
            }
            if (ddl_type.Text == "备品采购单")
            {
                PRepair_list.Visible = true;
                ERepair_list.Visible = false;
                try
                {
                    DataSet ds = new Catering_Rules.EMASppPlanP().GetList(strTmp);
                    DataTable dt_Plan_old = ds.Tables[0];
                    //除去重复的行
                    DataTable dt_Plan = dt_Plan_old.Clone();
                    foreach (DataRow dr in dt_Plan_old.Rows)
                    {
                        DataRow dr_Plan = dt_Plan.NewRow();

                        bool bFlag = true;
                        for (int nCount = 0; nCount < dt_Plan.Rows.Count; nCount++)
                        {
                            if (dr["ClassID"].ToString() == dt_Plan.Rows[nCount]["ClassID"].ToString() &&
                                dr["ContractorID"].ToString() == dt_Plan.Rows[nCount]["ContractorID"].ToString() &&
                                dr["AddDate"].ToString() == dt_Plan.Rows[nCount]["AddDate"].ToString())
                                bFlag = false;
                        }
                        if (bFlag)
                        {
                            foreach (DataColumn dc in dt_Plan_old.Columns)
                            {
                                dr_Plan[dc.ColumnName] = dr[dc.ColumnName];
                            }
                            dt_Plan.Rows.Add(dr_Plan);
                        }
                    }
                    dt_Plan.Columns.Add("tempId");
                    dt_Plan.Columns.Add("ClassName");
                    dt_Plan.Columns.Add("ContractorName");
                    dt_Plan.Columns.Add("ItemsDate");

                    for (int i = 0; i < dt_Plan.Rows.Count; i++)
                    {
                        dt_Plan.Rows[i]["ClassName"] = SelCNameByCID(dt_Plan.Rows[i]["ClassID"].ToString()).Tables[0].Rows[0][0];
                        dt_Plan.Rows[i]["ContractorName"] = SelConNameByConID(dt_Plan.Rows[i]["ContractorID"].ToString()).Tables[0].Rows[0][0];
                        dt_Plan.Rows[i]["tempId"] = i + 1;
                        dt_Plan.Rows[i]["ItemsDate"] = Convert.ToDateTime(dt_Plan.Rows[i]["AddDate"].ToString()).ToString("yyyy/MM/dd");
                    }
                    this.PRep_Plan.DataSource = dt_Plan;
                    this.PRep_Plan.DataBind();
                }
                catch
                {

                }
            }
        }
        protected void ERep_Plan_ItemCommand(object source, RepeaterCommandEventArgs e)
        {
            #region 设备报修单
            if (e.CommandName == "ERepair")
            {
                ArrayList al = new ArrayList();
                DataSet md = new Catering_Rules.EMASppPlanE().GetList(" ClassID =" + e.CommandArgument.ToString().Split(',')[0].ToString() + " and ContractorID =" + e.CommandArgument.ToString().Split(',')[1].ToString() + " and AddDate ='" + e.CommandArgument.ToString().Split(',')[2].ToString() + "'");
                DataTable dt_Plan = md.Tables[0];
                if (dt_Plan.Rows.Count == 0)
                {
                    string strError = "alert('查询数据为空!')";
                    ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "onekey", strError, true);
                    return;
                }
                dt_Plan.Columns.Add("ClassName");
                dt_Plan.Columns.Add("ContractorName");
                for (int i = 0; i < dt_Plan.Rows.Count; i++)
                {
                    dt_Plan.Rows[i]["ClassName"] = SelCNameByCID(dt_Plan.Rows[i]["ClassID"].ToString()).Tables[0].Rows[0][0];
                    dt_Plan.Rows[i]["ContractorName"] = SelConNameByConID(dt_Plan.Rows[i]["ContractorID"].ToString()).Tables[0].Rows[0][0];
                }
                string stick = DateTime.Now.Ticks.ToString();
                Microsoft.Office.Interop.Excel.Application excel1 = null;
                Microsoft.Office.Interop.Excel._Worksheet ws = null;
                Microsoft.Office.Interop.Excel.Range canbie = null;
                try
                {
                    excel1 = new Microsoft.Office.Interop.Excel.Application();
                    excel1.Application.Workbooks.Add(true);//引用Excel工作簿                
                    ws = new Microsoft.Office.Interop.Excel.WorksheetClass();
                    ws = (Microsoft.Office.Interop.Excel._Worksheet)excel1.ActiveSheet;//获取当前活动工作薄                
                    excel1.Visible = false;

                    #region  表头

                    canbie = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 8]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[1, 1] = Convert.ToDateTime(dt_Plan.Rows[0]["AddDate"].ToString()).ToString("yyyy/MM/dd") + "_" + dt_Plan.Rows[0]["ClassName"].ToString() + "_" + "设备报修单";

                    #endregion

                    #region 第一行标题
                    canbie = ws.get_Range(ws.Cells[2, 1], ws.Cells[2, 1]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 1] = "班组";
                    canbie = ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 2] = "承包商";
                    canbie = ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 3] = "编制日期";
                    canbie = ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 4] = "设备名称";
                    canbie = ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 5]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 5] = "设备型号";
                    canbie = ws.get_Range(ws.Cells[2, 6], ws.Cells[2, 6]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 6] = "品牌产地";
                    canbie = ws.get_Range(ws.Cells[2, 7], ws.Cells[2, 7]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 7] = "报修人";
                    canbie = ws.get_Range(ws.Cells[2, 8], ws.Cells[2, 8]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 8] = "设备故障具体描述";


                    #endregion

                    #region 主要内容
                    for (int nContent = 0; nContent < dt_Plan.Rows.Count; nContent++ )
                    {
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 1], ws.Cells[3 + nContent, 1]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 1] = dt_Plan.Rows[nContent]["ClassName"].ToString();
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 2], ws.Cells[3 + nContent, 2]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 2] = dt_Plan.Rows[nContent]["ContractorName"].ToString();
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 3], ws.Cells[3 + nContent, 3]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 3] = Convert.ToDateTime(dt_Plan.Rows[nContent]["AddDate"].ToString()).ToString("yyyy/MM/dd");
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 4], ws.Cells[3 + nContent, 4]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 4] = dt_Plan.Rows[nContent]["EquipCName"].ToString();
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 5], ws.Cells[3 + nContent, 5]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 5] = dt_Plan.Rows[nContent]["EquipModel"].ToString();
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 6], ws.Cells[3 + nContent, 6]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 6] = dt_Plan.Rows[nContent]["BrandArea"].ToString();
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 7], ws.Cells[3 + nContent, 7]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 7] = dt_Plan.Rows[nContent]["UserCName"].ToString();
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 8], ws.Cells[3 + nContent, 8]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 8] = dt_Plan.Rows[nContent]["ConDesc"].ToString();
                    }

                    ws.Cells.EntireColumn.AutoFit();
                    canbie = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 8]);
                    canbie.Borders.LineStyle = 1;
                    #endregion
                    if (!Directory.Exists(Server.MapPath("../../xls_template"))) Directory.CreateDirectory(Server.MapPath("../../xls_template"));
                    ws.SaveAs(Server.MapPath("../../xls_template") + "\\" + stick + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlAddIn8);
                }
                finally
                {
                    if (excel1 != null)
                    {
                        if (ws != null)
                        {
                            if (canbie != null)
                            {
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(canbie);
                                canbie = null;

                            }
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
                            ws = null;
                        }
                        excel1.Application.Workbooks.Close();
                        excel1.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1);
                        Toolss.KillExcel(excel1);
                        excel1 = null;
                        GC.Collect();
                    }
                }

                #region 发送

                try
                {
                    string path = Server.MapPath("../../xls_template") + "\\" + stick + ".xls";
                    System.IO.FileInfo file = new System.IO.FileInfo(path);
                    Response.Clear();
                    Response.Charset = "GB2312";
                    Response.ContentEncoding = System.Text.Encoding.UTF8;
                    //添加头信息，,弹出另存为窗口  
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
                    //让浏览器头显示下载信息  
                    Response.AddHeader("Content-Length", file.Length.ToString());
                    // 指定返回一个不能被客户端读取的流，下载  
                    Response.ContentType = "application/ms-excel";
                    //把文件流下载到客户端  
                    Response.WriteFile(file.FullName);
                    Toolss.DeleteFile(Server.MapPath("../../xls_template") + "\\" + stick + ".xls");
                    // 停止页面的执行
                    Response.End();
                }
                catch
                {
                }
                #endregion
            }
            if (e.CommandName == "del")
            {
                int delID = Convert.ToInt32(e.CommandArgument);
                if (new Catering_Rules.EMASppPlanE().Delete(delID))
                {
                    this.Search_Click(null, null);
                }
            }
            #endregion
            
        }
        protected void PRep_Plan_ItemCommand(object source, RepeaterCommandEventArgs e)
        {
            #region 备品采购单
            if (e.CommandName == "SppPlan")
            {
                ArrayList al = new ArrayList();
                DataSet md = new Catering_Rules.EMASppPlanP().GetList(" ClassID =" + e.CommandArgument.ToString().Split(',')[0].ToString() + " and ContractorID =" + e.CommandArgument.ToString().Split(',')[1].ToString() + " and AddDate ='" + e.CommandArgument.ToString().Split(',')[2].ToString() + "'");
                DataTable dt_Plan = md.Tables[0];
                if (dt_Plan.Rows.Count == 0)
                {
                    string strError = "alert('查询数据为空!')";
                    ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "onekey", strError, true);
                    return;
                }
                dt_Plan.Columns.Add("ClassName");
                dt_Plan.Columns.Add("ContractorName");
                for (int i = 0; i < dt_Plan.Rows.Count; i++)
                {
                    dt_Plan.Rows[i]["ClassName"] = SelCNameByCID(dt_Plan.Rows[i]["ClassID"].ToString()).Tables[0].Rows[0][0];
                    dt_Plan.Rows[i]["ContractorName"] = SelConNameByConID(dt_Plan.Rows[i]["ContractorID"].ToString()).Tables[0].Rows[0][0];
                }
                string stick = DateTime.Now.Ticks.ToString();
                Microsoft.Office.Interop.Excel.Application excel1 = null;
                Microsoft.Office.Interop.Excel._Worksheet ws = null;
                Microsoft.Office.Interop.Excel.Range canbie = null;
                try
                {
                    excel1 = new Microsoft.Office.Interop.Excel.Application();
                    excel1.Application.Workbooks.Add(true);//引用Excel工作簿                
                    ws = new Microsoft.Office.Interop.Excel.WorksheetClass();
                    ws = (Microsoft.Office.Interop.Excel._Worksheet)excel1.ActiveSheet;//获取当前活动工作薄                
                    excel1.Visible = false;

                    #region  表头

                    canbie = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 8]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[1, 1] = Convert.ToDateTime(dt_Plan.Rows[0]["AddDate"].ToString()).ToString("yyyy/MM/dd") + "_" + dt_Plan.Rows[0]["ClassName"].ToString() + "_" + "备件采购单";

                    #endregion

                    #region 第一行标题
                    canbie = ws.get_Range(ws.Cells[2, 1], ws.Cells[2, 1]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 1] = "班组";
                    canbie = ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 2] = "承包商";
                    canbie = ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 3] = "编制日期";
                    canbie = ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 4] = "产品名称";
                    canbie = ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 5]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 5] = "产品型号";
                    canbie = ws.get_Range(ws.Cells[2, 6], ws.Cells[2, 6]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 6] = "计量单位";
                    canbie = ws.get_Range(ws.Cells[2, 7], ws.Cells[2, 7]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 7] = "产品数量";
                    canbie = ws.get_Range(ws.Cells[2, 8], ws.Cells[2, 8]);
                    canbie.MergeCells = true;//合并单元格
                    canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    canbie.Font.Bold = 2;
                    ws.Cells[2, 8] = "备注";


                    #endregion

                    #region 主要内容
                    //canbie = ws.get_Range(ws.Cells[3, 1], ws.Cells[3, 1]);
                    //canbie.MergeCells = true;//合并单元格
                    //canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    //canbie.Font.Bold = 0;
                    //ws.Cells[3, 1] = dt_Plan.Rows[0]["ClassName"].ToString();
                    //canbie = ws.get_Range(ws.Cells[3, 2], ws.Cells[3, 2]);
                    //canbie.MergeCells = true;//合并单元格
                    //canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    //canbie.Font.Bold = 0;
                    //ws.Cells[3, 2] = dt_Plan.Rows[0]["ContractorName"].ToString();
                    //canbie = ws.get_Range(ws.Cells[3, 3], ws.Cells[3, 3]);
                    //canbie.MergeCells = true;//合并单元格
                    //canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    //canbie.Font.Bold = 0;
                    //ws.Cells[3, 3] = Convert.ToDateTime(dt_Plan.Rows[0]["AddDate"].ToString()).ToString("yyyy/MM/dd");
                    //canbie = ws.get_Range(ws.Cells[3, 4], ws.Cells[3, 4]);
                    //canbie.MergeCells = true;//合并单元格
                    //canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    //canbie.Font.Bold = 0;
                    //ws.Cells[3, 4] = dt_Plan.Rows[0]["AccessName"].ToString();
                    //canbie = ws.get_Range(ws.Cells[3, 5], ws.Cells[3, 5]);
                    //canbie.MergeCells = true;//合并单元格
                    //canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    //canbie.Font.Bold = 0;
                    //ws.Cells[3, 5] = dt_Plan.Rows[0]["ModelRuler"].ToString();
                    //canbie = ws.get_Range(ws.Cells[3, 6], ws.Cells[3, 6]);
                    //canbie.MergeCells = true;//合并单元格
                    //canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    //canbie.Font.Bold = 0;
                    //ws.Cells[3, 6] = dt_Plan.Rows[0]["AccessUnit"].ToString();
                    //canbie = ws.get_Range(ws.Cells[3, 7], ws.Cells[3, 7]);
                    //canbie.MergeCells = true;//合并单元格
                    //canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    //canbie.Font.Bold = 0;
                    //ws.Cells[3, 7] = dt_Plan.Rows[0]["AccessNumber"].ToString();
                    //canbie = ws.get_Range(ws.Cells[3, 8], ws.Cells[3, 8]);
                    //canbie.MergeCells = true;//合并单元格
                    //canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                    //canbie.Font.Bold = 0;
                    //ws.Cells[3, 8] = dt_Plan.Rows[0]["ConDesc"].ToString();
                    for (int nContent = 0; nContent < dt_Plan.Rows.Count; nContent++)
                    {
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 1], ws.Cells[3 + nContent, 1]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 1] = dt_Plan.Rows[nContent]["ClassName"].ToString();
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 2], ws.Cells[3 + nContent, 2]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 2] = dt_Plan.Rows[nContent]["ContractorName"].ToString();
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 3], ws.Cells[3 + nContent, 3]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 3] = Convert.ToDateTime(dt_Plan.Rows[nContent]["AddDate"].ToString()).ToString("yyyy/MM/dd");
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 4], ws.Cells[3 + nContent, 4]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 4] = dt_Plan.Rows[nContent]["AccessName"].ToString();
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 5], ws.Cells[3 + nContent, 5]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 5] = dt_Plan.Rows[nContent]["ModelRuler"].ToString();
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 6], ws.Cells[3 + nContent, 6]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 6] = dt_Plan.Rows[nContent]["AccessUnit"].ToString();
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 7], ws.Cells[3 + nContent, 7]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 7] = dt_Plan.Rows[nContent]["AccessNumber"].ToString();
                        canbie = ws.get_Range(ws.Cells[3 + nContent, 8], ws.Cells[3 + nContent, 8]);
                        canbie.MergeCells = true;//合并单元格
                        canbie.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中
                        canbie.Font.Bold = 0;
                        ws.Cells[3 + nContent, 8] = dt_Plan.Rows[nContent]["ConDesc"].ToString();
                    }

                    ws.Cells.EntireColumn.AutoFit();
                    canbie = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 8]);
                    canbie.Borders.LineStyle = 1;
                    #endregion
                    if (!Directory.Exists(Server.MapPath("../../xls_template"))) Directory.CreateDirectory(Server.MapPath("../../xls_template"));
                    ws.SaveAs(Server.MapPath("../../xls_template") + "\\" + stick + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlAddIn8);
                }
                finally
                {
                    if (excel1 != null)
                    {
                        if (ws != null)
                        {
                            if (canbie != null)
                            {
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(canbie);
                                canbie = null;

                            }
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
                            ws = null;
                        }
                        excel1.Application.Workbooks.Close();
                        excel1.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1);
                        Toolss.KillExcel(excel1);
                        excel1 = null;
                        GC.Collect();
                    }
                }

                #region 发送

                try
                {
                    string path = Server.MapPath("../../xls_template") + "\\" + stick + ".xls";
                    System.IO.FileInfo file = new System.IO.FileInfo(path);
                    Response.Clear();
                    Response.Charset = "GB2312";
                    Response.ContentEncoding = System.Text.Encoding.UTF8;
                    //添加头信息，,弹出另存为窗口  
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
                    //让浏览器头显示下载信息  
                    Response.AddHeader("Content-Length", file.Length.ToString());
                    // 指定返回一个不能被客户端读取的流，下载  
                    Response.ContentType = "application/ms-excel";
                    //把文件流下载到客户端  
                    Response.WriteFile(file.FullName);
                    Toolss.DeleteFile(Server.MapPath("../../xls_template") + "\\" + stick + ".xls");
                    // 停止页面的执行
                    Response.End();
                }
                catch
                {
                }
                #endregion
            }
            if (e.CommandName == "del")
            {
                int delID = Convert.ToInt32(e.CommandArgument);
                if (new Catering_Rules.EMASppPlanP().Delete(delID))
                {
                    this.Search_Click(null, null);
                }
            }
            #endregion
        }
    }
}